import pandas as pd
import numpy as np
from sqlalchemy import create_engine
from datetime import timedelta
import requests
import tqdm

# ====== PostgreSQL 连接 ======
engine = create_engine(
    "postgresql+psycopg2://postgres:pass0907!@www.1026.tech:5430/cypcv4"
)

# ====== 加载历史真实数据（你的表结构版） ======
def load_history():
    sql = """
        SELECT time AS ts, electricity_num AS value
        FROM cost_electricity_input
        WHERE electricity_num IS NOT NULL
        ORDER BY time;
    """
    return pd.read_sql(sql, engine)

# ====== 你的 API 预测函数 ======
def model_predict_96(history_values, device_id="admin"):

    # 历史输入拼成字符串，比如 "1,2,3,4,5..."
    history_str = ",".join(map(str, history_values))

    url = f"http://localhost:5001/api/predict/electricity"
    params = {
        "device_id": device_id,
        "history": history_str
    }

    try:
        res = requests.get(
            url,
            params=params,
            timeout=10,
            proxies={"http": "", "https": ""}  # 禁用代理
        )

        print("RAW API:", res.text[:200])  # 调试用

        data = res.json()
        preds = data["data"]["predictions"]

        return pd.DataFrame([
            {"ts": p["time"], "pred_value": p["electricity_num"]}
            for p in preds
        ])

    except Exception as e:
        print("预测 API 调用失败:", e)
        print("返回内容:", res.text if 'res' in locals() else "无响应")
        return None


# ========== 误差指标 ==========
def calc_metrics(real, pred):
    diff = real - pred
    return {
        "MAE": np.mean(np.abs(diff)),
        "RMSE": np.sqrt(np.mean(diff**2)),
        "MAPE(%)": np.mean(np.abs(diff / real)) * 100,
        "R2": 1 - (diff.var() / real.var()),
    }


# ========== 回测过程（滑动窗口） ==========
def run_backtest(history_length=288, pred_length=96):
    df = load_history()

    # 真实数据时间解析
    df["ts"] = pd.to_datetime(df["ts"], errors="coerce", format="mixed")
    df = df.dropna(subset=["ts"])
    df = df.sort_values("ts").reset_index(drop=True)

    results = []

    for i in range(len(df) - history_length - pred_length):
        hist = df.iloc[i:i + history_length]
        real = df.iloc[i + history_length:i + history_length + pred_length]

        pred_df = model_predict_96(hist["value"].values)

        if pred_df is None or len(pred_df) == 0:
            continue

        # 预测数据时间解析
        pred_df["ts"] = pd.to_datetime(pred_df["ts"], errors="coerce", format="mixed")
        pred_df = pred_df.dropna(subset=["ts"])

        # 长度不对就跳过
        if len(pred_df) != pred_length:
            continue

        merged = pd.merge(real, pred_df, on="ts", how="inner")
        if len(merged) == 0:
            continue

        m = calc_metrics(merged["value"].values, merged["pred_value"].values)
        m["start_time"] = hist.iloc[-1]["ts"]
        results.append(m)

    return pd.DataFrame(results)


# ========== 主入口 ==========
if __name__ == "__main__":
    df_res = run_backtest(history_length=288, pred_length=96)

    print(df_res.head())
    print("\n=== 全局误差平均 ===")
    print(df_res.mean(numeric_only=True))